package com.pengy.shengxian.service;

import com.pengy.shengxian.entity.GoodsInfo;
import com.pengy.shengxian.entity.GoodsType;
import com.pengy.shengxian.mapper.GoodsInfoMapper;
import com.pengy.shengxian.mapper.GoodsTypeMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class GoodsInfoService {

    private static final Logger logger = LoggerFactory.getLogger(GoodsInfoService.class);

    @Autowired
    private GoodsInfoMapper goodsInfoMapper;

    @Autowired
    private GoodsTypeMapper goodsTypeMapper;

    @Autowired
    private DatabaseService databaseService;

    private Map<String, String> goodsTypeNameToCodeMap; // 商品品类名称到编码的映射

    @Autowired
    public GoodsInfoService() {
        this.goodsTypeNameToCodeMap = new HashMap<>();
    }

    /**
     * 加载商品品类映射关系
     */
    public void loadGoodsTypeMapping() {
        try {
            List<GoodsType> goodsTypes = goodsTypeMapper.selectAll();

            goodsTypeNameToCodeMap.clear();
            for (GoodsType goodsType : goodsTypes) {
                if (goodsType.getGoodsTypeName() != null && goodsType.getGoodsTypeCode() != null) {
                    goodsTypeNameToCodeMap.put(goodsType.getGoodsTypeName().trim(), goodsType.getGoodsTypeCode().trim());
                }
            }

            logger.info("成功加载 {} 个商品品类映射关系", goodsTypeNameToCodeMap.size());
        } catch (Exception e) {
            logger.error("加载商品品类映射关系失败", e);
            throw new RuntimeException("加载商品品类数据失败，请检查数据库连接和商品品类表", e);
        }
    }

    /**
     * 处理商品信息Excel文件
     * @param file Excel文件
     * @return 处理结果
     */
    public GoodsInfoProcessResult processGoodsInfoExcel(File file) {
        GoodsInfoProcessResult result = new GoodsInfoProcessResult();

        try {
            logger.info("开始处理商品信息文件: {}", file.getName());

            // 加载商品品类映射关系
            loadGoodsTypeMapping();

            // 读取Excel文件
            List<Map<String, Object>> excelData = readExcelFile(file);
            result.setTotalCount(excelData.size());

            // 转换为商品信息对象
            List<GoodsInfo> goodsInfoList = convertToGoodsInfoList(excelData);
            result.setProcessedData(goodsInfoList);

            // 检查是否有校验错误
            if (goodsInfoList.stream().anyMatch(g -> g.getGoodsInfoCode() == null || 
                                                   g.getGoodsInfoName() == null || 
                                                   g.getGoodsInfoType() == null || 
                                                   g.getGoodsInfoTypeCode() == null)) {
                throw new IllegalArgumentException("存在必填字段为空的商品信息，请检查日志获取详细信息");
            }

            // 保存到数据库
            int savedCount = databaseService.saveGoodsInfos(goodsInfoList);
            result.setSuccessCount(savedCount);
            result.setErrorCount(goodsInfoList.size() - savedCount);

            logger.info("商品信息文件处理完成: 总数={}, 成功={}, 失败={}",
                       result.getTotalCount(), result.getSuccessCount(), result.getErrorCount());

        } catch (Exception e) {
            logger.error("处理商品信息文件时发生错误", e);
            result.setErrorMessage(e.getMessage());
            result.setErrorCount(result.getTotalCount());
            result.setSuccessCount(0);
        }

        return result;
    }

    /**
     * 读取Excel文件
     */
    private List<Map<String, Object>> readExcelFile(File file) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);

            if (headerRow == null) {
                throw new RuntimeException("Excel文件第一行为空");
            }

            // 获取表头
            List<String> headers = new ArrayList<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                headers.add(cell != null ? cell.toString().trim() : "列" + (i + 1));
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                boolean hasData = false;

                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    Object value = getCellValue(cell);
                    rowData.put(headers.get(j), value);

                    if (value != null && !value.toString().trim().isEmpty()) {
                        hasData = true;
                    }
                }

                if (hasData) {
                    data.add(rowData);
                }
            }
        }

        return data;
    }

    /**
     * 转换Excel数据为商品信息对象
     */
    private List<GoodsInfo> convertToGoodsInfoList(List<Map<String, Object>> excelData) {
        List<GoodsInfo> goodsInfoList = new ArrayList<>();

        for (Map<String, Object> rowData : excelData) {
            try {
                GoodsInfo goodsInfo = new GoodsInfo();

                // 设置基本信息
                goodsInfo.setGoodsInfoDept("AHYJSX"); // 固定赋值
                goodsInfo.setGoodsInfoCusCode(getStringValue(rowData, "供应商编码"));
                goodsInfo.setGoodsInfoName(getStringValue(rowData, "商品名称"));
                goodsInfo.setGoodsInfoCode(getStringValue(rowData, "商品编码"));
                goodsInfo.setGoodsInfoSpecs(getStringValue(rowData, "规格"));
                goodsInfo.setGoodsInfoUnit(getStringValue(rowData, "单位"));

                // 设置商品品类信息
                String goodsTypeName = getStringValue(rowData, "商品品类");
                goodsInfo.setGoodsInfoType(goodsTypeName);

                // 根据商品品类名称查找编码
                String goodsTypeCode = goodsTypeNameToCodeMap.get(goodsTypeName != null ? goodsTypeName.trim() : "");
                goodsInfo.setGoodsInfoTypeCode(goodsTypeCode);

                // goodsInfoReserve2赋值为价格列
                Object priceCell = rowData.get("价格");
                String priceStr = null;
                if (priceCell != null) {
                    priceStr = priceCell.toString().trim();
                }
                goodsInfo.setGoodsInfoReserve2(priceStr);

                // goodsInfoReserve3赋值为税率列，兼容百分比单元格和字符串
                Object taxCell = rowData.get("税率");
                String taxStr = null;
                if (taxCell instanceof Number) {
                    double val = ((Number) taxCell).doubleValue() * 100;
                    taxStr = String.format("%.2f%%", val);
                } else if (taxCell != null) {
                    taxStr = taxCell.toString().trim();
                }
                goodsInfo.setGoodsInfoReserve3(taxStr);

                // 设置其他字段
                goodsInfo.setGoodsInfoWriDate(new Date());
                goodsInfo.setGoodsInfoEnable(1);
                goodsInfo.setGoodsInfoDeleted(0);
                goodsInfo.setGoodsInfoSyncStatus(0);
                // 校验必填字段
                if (StringUtils.isEmpty(goodsInfo.getGoodsInfoCode())) {
                    throw new IllegalArgumentException("第" + (excelData.indexOf(rowData) + 2) + "行商品编码不能为空");
                }
                if (StringUtils.isEmpty(goodsInfo.getGoodsInfoName())) {
                    throw new IllegalArgumentException("第" + (excelData.indexOf(rowData) + 2) + "行商品名称不能为空");
                }
                if (StringUtils.isEmpty(goodsInfo.getGoodsInfoType())) {
                    throw new IllegalArgumentException("第" + (excelData.indexOf(rowData) + 2) + "行商品类别不能为空");
                }
                if (StringUtils.isEmpty(goodsInfo.getGoodsInfoTypeCode())) {
                    throw new IllegalArgumentException("第" + (excelData.indexOf(rowData) + 2) + "行商品类别编码不能为空，可能是商品品类在系统中未找到对应编码");
                }
                goodsInfoList.add(goodsInfo);

            } catch (Exception e) {
                logger.error("转换商品信息数据时发生错误: {}", rowData, e);
                // 重新抛出校验异常，以便上层能够捕获并处理
                if (e instanceof IllegalArgumentException) {
                    throw e;
                }
            }
        }

        return goodsInfoList;
    }

    /**
     * 获取字符串值
     */
    private String getStringValue(Map<String, Object> data, String key) {
        Object value = data.get(key);
        if (value == null) {
            return null;
        }
        String strValue = value.toString().trim();
        return strValue.isEmpty() ? null : strValue;
    }

    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == (long) value) {
                        return (long) value;
                    } else {
                        return value;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    /**
     * 商品信息处理结果
     */
    public static class GoodsInfoProcessResult {
        private int totalCount;
        private int successCount;
        private int errorCount;
        private String errorMessage;
        private List<GoodsInfo> processedData;

        public GoodsInfoProcessResult() {
            this.processedData = new ArrayList<>();
        }

        // Getters and Setters
        public int getTotalCount() { return totalCount; }
        public void setTotalCount(int totalCount) { this.totalCount = totalCount; }

        public int getSuccessCount() { return successCount; }
        public void setSuccessCount(int successCount) { this.successCount = successCount; }

        public int getErrorCount() { return errorCount; }
        public void setErrorCount(int errorCount) { this.errorCount = errorCount; }

        public String getErrorMessage() { return errorMessage; }
        public void setErrorMessage(String errorMessage) { this.errorMessage = errorMessage; }

        public List<GoodsInfo> getProcessedData() { return processedData; }
        public void setProcessedData(List<GoodsInfo> processedData) { this.processedData = processedData; }
    }
}